package com.ist.word.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.ist.word.datasoure.SQLiteDataSource;
import com.ist.word.model.Word;

/**
 * @author liruoyu 2012-4-3 22:10:05
 */

public class WordDAO implements IBaseDAO {
	private int CountNo = 0;

	/**
	 * @param word
	 * @return word
	 * @throws Exception
	 */
	
	public Word selectByWord(Word word) {
		SQLiteDataSource linkDB = new SQLiteDataSource();
		Word resultWord = null;
		Connection conn=null;
		Statement worksStatement=null;
		ResultSet rs=null;
		try {
			conn = linkDB.getConneection();

			worksStatement = conn.createStatement();
			rs = worksStatement
					.executeQuery("select * from Words where Words='"
							+ word.getWords() + "'");
			if (!rs.isClosed()) {
				int wordNo = rs.getInt("WordNo");
				String wordsString = rs.getString("Words").toString();
				String phoneticString = rs.getString("Phonetic").toString();
				String translationString = rs.getString("Translation")
						.toString();
				resultWord = new Word();
				resultWord.setWordNo(wordNo);
				resultWord.setWords(wordsString);
				resultWord.setPhonetics(phoneticString);
				resultWord.setTranslation(translationString);

			} else {
				return null;
			}
			rs.close();
			worksStatement.close();
			conn.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
		}
		// TODO Auto-generated method stub
		return resultWord;
	}

	/**
	 * @param word
	 * @return word
	 * @throws Exception
	 */
	
	public Word selectByWord() {
		SQLiteDataSource linkDB = new SQLiteDataSource();
		Connection conn;
		Word resultWord = null;
		try {
			conn = linkDB.getConneection();

			Statement worksStatement = conn.createStatement();
			ResultSet rs = worksStatement
					.executeQuery("select * from Words where length(Words)=8 ORDER BY RANDOM() limit 1");
			if (rs.getRow() >= 1) {
				int wordNo = rs.getInt("WordNo");
				String wordsString = rs.getString("Words").toString();
				String phoneticString = rs.getString("Phonetic").toString();
				String translationString = rs.getString("Translation")
						.toString();
				resultWord = new Word();
				resultWord.setWordNo(wordNo);
				resultWord.setWords(wordsString);
				resultWord.setPhonetics(phoneticString);
				resultWord.setTranslation(translationString);
				rs.close();
				worksStatement.close();
				conn.close();
				
			} else {
				rs.close();
				worksStatement.close();
				conn.close();
				return null;

			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultWord;

	}

	public int getTotalRecord() {
		int totalRecord = 0;
		SQLiteDataSource linkDB = new SQLiteDataSource();
		Connection conn;
		Statement worksStatement;
		ResultSet rs;
		try {
			conn = linkDB.getConneection();

			worksStatement = conn.createStatement();
			rs = worksStatement.executeQuery("select count(*) from Words");
			totalRecord = rs.getInt(1);
			rs.close();
			worksStatement.close();
			conn.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return totalRecord;

	}

	public Word[] selectWords(int length) {
		SQLiteDataSource linkDB = new SQLiteDataSource();
		Word wordarray[] = new Word[length];
		Connection conn;
		Statement worksStatement;
		ResultSet rs;
		try {
			conn = linkDB.getConneection();

			worksStatement = conn.createStatement();
			rs = worksStatement
					.executeQuery("SELECT * FROM Words where length(Words)=8 ORDER BY RANDOM() limit "
							+ length);

			int i = 0;
			while (rs.next()) {
				//System.out.println(rs.getInt("WordNo"));
				wordarray[i]=new Word();
				wordarray[i].setWordNo(rs.getInt("WordNo"));
				wordarray[i].setWords(rs.getString("Words"));
				wordarray[i].setPhonetics(rs.getString("Phonetic"));
				wordarray[i].setTranslation(rs.getString("Translation"));
				i++;
			}
			rs.close();
			worksStatement.close();
			conn.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// return null;
		return wordarray;
	}

/*
	public static void main(String[] args) throws Exception {
		Word word = new Word();
		word.setWords("love");
		System.out.println(new WordDAO().selectByWord(word));
		System.out.println(new WordDAO().selectByWord().getWords());
		System.out.println(new WordDAO().getTotalRecord());
		System.out.println(new WordDAO().selectWords(8).length);
	}
	*/

}
